
#include "sedona.h"

#include <stdlib.h>
#include <stdio.h>
#include <assert.h>
#include <memory.h>
#include <wchar.h>


#include <sqlite3.h>
#include <sys/time.h>
#include <time.h>
#include <string.h>

static struct tm *pt;
static int cur_sec, cur_min, cur_hour, cur_day, cur_month, cur_year, cur_weekday;
sqlite3_stmt *stmt;

static char sql1[] = "CREATE TABLE IF NOT EXISTS datas_10min(id integer PRIMARY KEY, time integer NOT NULL,"
					"inTmp float NOT NULL, outTmp float NOT NULL, inHum float NOT NULL," 
					"outHum float NOT NULL, boxTmp float NOT NULL, AC0Tmp float NOT NULL,"
					"AC1Tmp float NOT NULL, powerMain float NOT NULL, energyMain float NOT NULL,"
					"powerDC float NOT NULL, energyDC float NOT NULL,"
					"fan0State integer NOT NULL, fan1State integer NOT NULL,"
					"AC0State integer NOT NULL, AC1State integer NOT NULL, miniACState integer NOT NULL)";
static char sql2[] = "CREATE TABLE IF NOT EXISTS datas_hour(id integer PRIMARY KEY, time integer NOT NULL,"
					"inTmp float NOT NULL, outTmp float NOT NULL, inHum float NOT NULL," 
					"outHum float NOT NULL, boxTmp float NOT NULL, AC0Tmp float NOT NULL,"
					"AC1Tmp float NOT NULL, powerMain float NOT NULL, energyMain float NOT NULL,"
					"powerDC float NOT NULL, energyDC float NOT NULL,"
					"fan0State integer NOT NULL, fan1State integer NOT NULL,"
					"AC0State integer NOT NULL, AC1State integer NOT NULL, miniACState integer NOT NULL)";
static char sql3[] =  "CREATE TABLE IF NOT EXISTS datas_day(id integer PRIMARY KEY, time integer NOT NULL,"
					"inTmp float NOT NULL, outTmp float NOT NULL, inHum float NOT NULL," 
					"outHum float NOT NULL, boxTmp float NOT NULL, AC0Tmp float NOT NULL,"
					"AC1Tmp float NOT NULL, powerMain float NOT NULL, energyMain float NOT NULL,"
					"powerDC float NOT NULL, energyDC float NOT NULL,"
					"fan0State integer NOT NULL, fan1State integer NOT NULL,"
					"AC0State integer NOT NULL, AC1State integer NOT NULL, miniACState integer NOT NULL)";
static char sql4[1000] ;
static char sql5[100]="SELECT ID,upload FROM MyTable_1";
static char sql6[100];
static char sql7[1000];
static sqlite3 *db;
static int result;
static char *errmsg = NULL;
static unsigned long size;
#define SELECT_MIN_ID 	1
#define SELECT_MAX_ID 	2
#define SELECT_COUNT	3
#define SELECT_MIN_TIME 4
#define SELECT_MAX_TIME 5


unsigned long get_file_size(const char *path)
{
        unsigned long filesize = -1;
        struct stat statbuff;
        if(stat(path, &statbuff) < 0){
                return filesize;
        }else{
                filesize = statbuff.st_size;
        }
      //  printf("the size of file is %ld\n",filesize);
        return filesize;
}

void genInsertStatementModelOne(float * envVar, int * contrlState)
{
    time_t curr_time = time(NULL);

	memset(sql7, 0, sizeof(sql7));
    sprintf(sql7, "INSERT INTO datas_10min(time, inTmp, OutTmp, inHum, outHum,"
			"boxTmp, AC0Tmp, AC1Tmp, powerMain, energyMain, powerDC, energyDC,"
			"fan0State, fan1State, AC0State, AC1State, miniACState)"
			"VALUES(%d, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %d, %d, %d, %d, %d)", 
			(int)curr_time, envVar[0], envVar[1], envVar[2], envVar[3], envVar[4], envVar[5],
			envVar[6], envVar[7], envVar[8], envVar[9], envVar[10], contrlState[0], contrlState[1],
			contrlState[2], contrlState[3], contrlState[4]);
}

void genInsertStatementModelTwo(int stationID, char * jsondata, int tableType)
{
    time_t curr_time = time(NULL);
    
	memset(sql7, 0, sizeof(sql7));
    if (tableType == 1) {
    		sprintf(sql7, "INSERT INTO datas_10min(time, stationID, DataInJsonFormat)"
			"VALUES(%d, %d, '%s')", (int)curr_time, stationID, jsondata);
    	} else if (tableType == 2) {
		sprintf(sql7, "INSERT INTO datas_hour(time, stationID, DataInJsonFormat)"
			"VALUES(%d, %d, '%s')", (int)curr_time, stationID, jsondata);		
 	} else {
		sprintf(sql7, "INSERT INTO datas_day(time, stationID, DataInJsonFormat)"
			"VALUES(%d, %d, '%s')", (int)curr_time, stationID, jsondata);
	}
}

void genSelectStatementModelOne(int tableType, int startTime, int endTime)
{
	memset(sql7, 0, sizeof(sql7));
	
	if (tableType == 1) {
		sprintf(sql7, "select * from datas_10min where time >= %d AND time <= %d;", startTime, endTime);
	} else if (tableType == 2) {
		sprintf(sql7, "select * from datas_hour where time >= %d AND time <= %d;", startTime, endTime);
	} else if (tableType == 3) {
		sprintf(sql7, "select * from datas_day where time >= %d AND time <= %d;", startTime, endTime);
	} 

	return;
}

void genSelectStatementModelTwo(int tableType, int  selectType)
{
	memset(sql7, 0, sizeof(sql7));
	if (tableType == 1) {
		if (selectType == SELECT_MIN_TIME) {
			sprintf(sql7, "SELECT * from datas_10min where time = (select max(time) from datas_10min);");
		} else if (selectType == SELECT_MAX_TIME) {
			sprintf(sql7, "SELECT * from datas_10min where time = (select min(time) from datas_10min);");
		} else if (selectType == SELECT_COUNT) { 
			sprintf(sql7, "select count(*) from datas_10min;");	
		}
	} else if (tableType == 2) {
		if (selectType == SELECT_MIN_TIME) {
			sprintf(sql7, "SELECT * from datas_hour where time = (select max(time) from datas_hour);");
		} else if (selectType == SELECT_MAX_TIME) {
			sprintf(sql7, "SELECT * from datas_hour where time = (select min(time) from datas_hour);");
		} else if (selectType == SELECT_COUNT) { 
			sprintf(sql7, "select count(*) from datas_hour;");	
		}
	} else if (tableType == 3) {
		if (selectType == SELECT_MIN_TIME) {
			sprintf(sql7, "SELECT * from datas_day where time = (select max(time) from datas_day);");
		} else if (selectType == SELECT_MAX_TIME) {
			sprintf(sql7, "SELECT * from datas_day where time = (select min(time) from datas_day);");
		} else if (selectType == SELECT_COUNT) { 
			sprintf(sql7, "select count(*) from datas_day;");	
		}
	} else {
		printf("table not support: %d\n", tableType);
	}
}

void genCreateStatementModelOne(int tableType)
{
	memset(sql4, 0, sizeof(sql4));
	if (tableType == 1) {
		sprintf(sql4, "CREATE TABLE IF NOT EXISTS datas_10min(id integer PRIMARY KEY, time integer NOT NULL,"
			         "stationID integer NOT NULL, DataInJsonFormat varchar(1000) NOT NULL);"
			         "CREATE INDEX IF NOT EXISTS time_idx  on datas_10min(time);");	
	} else if (tableType == 2) {
		sprintf(sql4, "CREATE TABLE IF NOT EXISTS datas_hour(id integer PRIMARY KEY, time integer NOT NULL,"
			         "stationID integer NOT NULL, DataInJsonFormat varchar(1000) NOT NULL);"
			         "CREATE INDEX IF NOT EXISTS time_idx  on datas_hour(time);");	
	} else {
		sprintf(sql4, "CREATE TABLE IF NOT EXISTS datas_day(id integer PRIMARY KEY, time integer NOT NULL,"
			         "stationID integer NOT NULL, DataInJsonFormat varchar(1000) NOT NULL);"
			         "CREATE INDEX IF NOT EXISTS time_idx  on datas_day(time);");	
	}

}


/*int generateSQL2()
{
    time_t t = time(NULL);
    pt = localtime(&t);

    cur_sec = pt->tm_sec;
    cur_min = pt->tm_min;
    cur_hour = pt->tm_hour;
    cur_day = pt->tm_mday;
    cur_month = pt->tm_mon + 1;
    cur_year = pt->tm_year + 1900;
    cur_weekday = pt->tm_wday;
    sprintf(sql11,"INSERT INTO MyTable_2(time,command,download) VALUES('%d-%d-%d,%d:%d:%d','%s',%d)",cur_year,cur_month,cur_day,cur_hour,cur_min,cur_sec,sql8,g);
}*/

Cell sqlite_SqliteDatabaseStd_sqlitePrepare(SedonaVM* vm, Cell* params)
{
	int rc;
	const char *tail;
	int  tableType = params[1].ival;
	int  startTime   = params[2].ival;
	int  endTime 	   = params[3].ival;
	Cell ret;

	if (tableType < 1 || tableType > 3) {
		printf("table type [%d] unsupported \n", tableType);
		return negOneCell;
	}

	genSelectStatementModelOne(tableType, startTime, endTime);

	// muti statement can be used, but  only  the first statement can be executed.
	rc = sqlite3_prepare_v2(db, sql7, (int)strlen(sql7), &stmt, &tail);
	if(rc != SQLITE_OK) {     
		fprintf(stderr, "sqlite_SqliteDatabaseStd_sqlitePrepare error: %s\n", sqlite3_errmsg(db));
		ret.ival = rc;
		return ret;
	}

	ret.ival = SQLITE_OK;
	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteStep(SedonaVM* vm, Cell* params)
{
	Cell ret;

	ret.ival = sqlite3_step(stmt);
	printf("\n sqlite_step return value %d\n", ret.ival);
	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteFinalize(SedonaVM* vm, Cell* params)
{
	sqlite3_finalize(stmt);
	
	return zeroCell;
}

Cell sqlite_SqliteDatabaseStd_SqliteGetColumnInt(SedonaVM* vm, Cell* params)
{
	Cell ret;

	ret.ival = sqlite3_column_int(stmt, params[1].ival);
	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteGetColumnText(SedonaVM* vm, Cell* params)
{
	strcpy((char *)params[1].aval, sqlite3_column_text(stmt, params[2].ival));

	return zeroCell;	
}

Cell sqlite_SqliteDatabaseStd_SqliteGetColumnFloat(SedonaVM* vm, Cell* params)
{
	Cell ret;

	ret.fval = (float)sqlite3_column_double(stmt, params[1].ival);
	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteColumnCount(SedonaVM* vm, Cell* params)
{
	Cell ret;

	ret.ival = sqlite3_column_count(stmt);
	return ret;
}



Cell sqlite_SqliteDatabaseStd_SqliteLookupTimeMax(SedonaVM* vm, Cell* params) 
{
	int selectType = SELECT_MAX_TIME;
	int typeType = (int)params[1].ival;
	const char *tail;
	int rc;
	Cell ret;
	
	genSelectStatementModelTwo(typeType, selectType);

	rc = sqlite3_prepare_v2(db, sql7, (int)strlen(sql7), &stmt, &tail);
	if(rc != SQLITE_OK) {	  
		fprintf(stderr, "sqlite_SqliteDatabaseStd_SqliteLookupTimeMax error: %s\n", sqlite3_errmsg(db));
		ret.ival = rc;
		return ret;
	}

	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW) {
		ret.ival = sqlite3_column_int(stmt, 1);
	}

	sqlite3_finalize(stmt);

	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteLookupTimeMin(SedonaVM* vm, Cell* params) 
{
	int selectType = SELECT_MIN_TIME;
	int typeType = (int)params[1].ival;
	const char *tail;
	int rc;
	Cell ret;
	
	genSelectStatementModelTwo(typeType, selectType);

	rc = sqlite3_prepare_v2(db, sql7, (int)strlen(sql7), &stmt, &tail);
	if(rc != SQLITE_OK) {	  
		fprintf(stderr, "sqlite_SqliteDatabaseStd_sqlitePrepareSqliteLookupTimeMin error: %s\n", sqlite3_errmsg(db));
		ret.ival = rc;
		return ret;
	}

	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW) {
		ret.ival = sqlite3_column_int(stmt, 1);
	}

	sqlite3_finalize(stmt);

	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteGetTableSize(SedonaVM* vm, Cell* params) 
{
	int selectType = SELECT_COUNT;
	int typeType = (int)params[1].ival;
	const char *tail;
	int rc;
	Cell ret;
	
	genSelectStatementModelTwo(typeType, selectType);

	rc = sqlite3_prepare_v2(db, sql7, (int)strlen(sql7), &stmt, &tail);
	if(rc != SQLITE_OK) {	  
		fprintf(stderr, "sqlite_SqliteDatabaseStd_SqliteGetTableSize error: %s\n", sqlite3_errmsg(db));
		ret.ival = rc;
		return ret;
	}

	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW) {
		ret.ival = sqlite3_column_int(stmt, 0);
	}

	sqlite3_finalize(stmt);

	return ret;
}

Cell sqlite_SqliteDatabaseStd_SqliteDeleteRecords(SedonaVM* vm, Cell* params)
{
	int tblType = params[1].ival;
	int startTime = params[2].ival;
	int endTime = params[3].ival;

	genSelectStatementModelOne(tblType, startTime, endTime);

	result = sqlite3_exec(db, sql7, NULL, NULL, &errmsg);
	if (result != SQLITE_OK) {
      		printf("Unsuccessfully exec sqlite DB, error code:%d, Reason of error:%s\n",result,errmsg);
	   	return negOneCell;
	}
	
    	return zeroCell;
}


int sqliteExecCallback(void *para, int n_column, char **column_value, char **column_name)
{
   int i;
   printf("Record contain %d field\n",n_column);
   for(i=0;i<n_column;i++)
   {
      	printf( "Name of field:%s,Value of field:%s\n",column_name[i],column_value[i]);         }
		printf( "------------------\n");
	return 0;
}


Cell sqlite_SqliteDatabaseStd_sqliteLookup(SedonaVM* vm, Cell* params) 
{
	char * output = (char *)params[1].aval;
	int  tableType = params[2].ival;
	int  startTime   = params[3].ival;
	int  endTime 	   = params[4].ival;

	if (tableType < 1 || tableType > 3) {
		printf("table type [%d] unsupported \n", tableType);
		return negOneCell;
	}

	genSelectStatementModelOne(tableType, startTime, endTime);

	result = sqlite3_exec(db, sql7, sqliteExecCallback, output, &errmsg);
	if(result!=SQLITE_OK)
	{
       printf( "Unsuccessfully,error code:%d,Reason of error:%s\n",result,errmsg);
	   return negOneCell;
	}

	
    return zeroCell;
		
}

Cell sqlite_SqliteDatabaseStd_sqliteInsertStatement(SedonaVM* vm, Cell* params)
{
	int 	  stationID  = params[1].ival;
	char * jsondata = (char *) params[2].aval;
	int    tableType  = (int)params[3].ival;

	genInsertStatementModelTwo(stationID, jsondata, tableType);

	result = sqlite3_exec(db, sql7, NULL, NULL, &errmsg);
	if (result != SQLITE_OK) {
      		printf("Unsuccessfully exec sqlite DB, error code:%d, Reason of error:%s\n",result,errmsg);
	   	return negOneCell;
	}

	
    	return zeroCell;
}


Cell sqlite_SqliteDatabaseStd_sqliteCloseDb(SedonaVM* vm, Cell* params)
{
	//char* dbFilename  = params[1].aval;
	 
	sqlite3_close(db);
	
    	return zeroCell;
}

Cell sqlite_SqliteDatabaseStd_sqliteOpenDb(SedonaVM* vm, Cell* params)
{
	 char* dbFilename  = params[1].aval;
	 int tableType = params[2].ival;

	result = sqlite3_open(dbFilename, &db);
	if(result != SQLITE_OK)
	{
		printf("Unsuccessfully open DB, error code:%d",result);
		sqlite3_close(db);
		return negOneCell;
	}

	genCreateStatementModelOne(tableType);
	
	result = sqlite3_exec(db, sql4, NULL, NULL, &errmsg);	
	if (result != SQLITE_OK) {
		if (errmsg != NULL) {
			printf("Unsuccessfully exec sqlite DB, error code:%d, Reason of error:%s\n",result, errmsg);
			sqlite3_free(errmsg);
		}
		
		return negOneCell;
	}

	 	
   	return zeroCell;
}



